package dao.impl;

import dao.BookDao;
import entity.Book;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;
import util.DataSourceUtil;
import util.Util;

import java.sql.SQLException;
import java.util.List;

public class BookDaoImpl implements BookDao {
    public boolean isExist(String bookId) throws SQLException {
        QueryRunner runner=new QueryRunner(DataSourceUtil.getDataSource());
        String sql="select count(*) from books where bookId=?";
        Long count=runner.query(sql,new ScalarHandler<Long>(),bookId);
        if(count>0){
            return true;
        }
        else {
            return false;
        }
    }

    public boolean isIn(String bookId) throws SQLException {
        QueryRunner runner=new QueryRunner(DataSourceUtil.getDataSource());
        String sql="select count(*) from books where bookId=? and exist=?";
        Object[] params={bookId,"true"};
        Long count=runner.query(sql,new ScalarHandler<Long>(),params);
        if(count>0){
            return true;
        }else{
            return false;
        }
    }

    public boolean updateStat(String bookId,String state) throws SQLException {
        QueryRunner runner=new QueryRunner(DataSourceUtil.getDataSource());
        String sql="update books set exist=? where bookId=?";
        Object[] params={state,bookId};
        int result=runner.update(sql,params);
        if(result>0){
            return true;
        }else{
            return false;
        }
    }

    public boolean addBook(Book book) throws SQLException {
        QueryRunner runner=new QueryRunner(DataSourceUtil.getDataSource());
        String sql="insert into books values(?,?,?,?,?,?,?)";
        Object[] params={book.getBookId(),book.getBookName(),book.getPublisher(),book.getPrice(),book.getAuthor(),"true", Util.getDate()};
        int result=runner.update(sql,params);
        if(result>0){
            return true;
        }else{
            return false;
        }
    }


    public List<Book> queryAllBooks() throws SQLException {
        QueryRunner runner=new QueryRunner(DataSourceUtil.getDataSource());
        String sql="select * from books";
        return runner.query(sql,new BeanListHandler<Book>(Book.class));
    }

    public Book queryBookById(String bookId) throws SQLException {
        QueryRunner runner=new QueryRunner(DataSourceUtil.getDataSource());
        String sql="select * from books where bookId=?";
        return runner.query(sql,new BeanHandler<Book>(Book.class),bookId);
    }


    public boolean deleteBookById(String bookId) throws SQLException {
        QueryRunner runner=new QueryRunner(DataSourceUtil.getDataSource());
        String sql="delete from books where bookId=?";
        int result=runner.update(sql,bookId);
        if(result>0){
            return true;
        }else{
            return false;
        }
    }

    public boolean updateBookById(String bookId, Book book) throws SQLException {
        QueryRunner runner=new QueryRunner(DataSourceUtil.getDataSource());
        String sql="update books set bookName=? ,publisher=? ,price=? ,author=? where bookId=?";
        Object[] params={book.getBookName(),book.getPublisher(),book.getPrice(),book.getAuthor(),bookId};
        int result=runner.update(sql,params);
        if(result>0){
            return true;
        }else{
            return false;
        }
    }

    public List<Book> queryBookByPage(int currentPage, int pageSize) throws SQLException {
        QueryRunner runner=new QueryRunner(DataSourceUtil.getDataSource());
        String sql="select * from books limit ?,?";
        Object[] params={currentPage*pageSize,pageSize};
        return runner.query(sql,new BeanListHandler<Book>(Book.class),params);
    }

    public int getTotalCount(String sql,Object[] params) throws SQLException {
        return Util.getCountTotal(sql,params).intValue();
    }

    public List<Book> queryBookByPageRequire(int currentPage,int pageSize,Object param) throws SQLException{
        QueryRunner runner=new QueryRunner(DataSourceUtil.getDataSource());
        String sql="select distinct * from books where bookId=? or bookName=? limit ?,?";
        Object[] params={param,param,currentPage*pageSize,pageSize};
        return runner.query(sql,new BeanListHandler<Book>(Book.class),params);
    }

    public List<Book> queryBorrowBookByPage(int currentPage,int pageSize) throws SQLException{
        QueryRunner runner=new QueryRunner(DataSourceUtil.getDataSource());
        String sql="select * from books where exist=? limit ?,?";
        Object[] params={"true",currentPage*pageSize,pageSize};
        return runner.query(sql,new BeanListHandler<Book>(Book.class),params);
    }

    public List<Book> queryBorrowBookByPageRequire(int currentPage,int pageSize,Object param) throws SQLException{
        QueryRunner runner=new QueryRunner(DataSourceUtil.getDataSource());
        String sql="select * from books where exist=? and (bookId=? or bookName=?) limit ?,?";
        Object[] params={"true",param,param,currentPage*pageSize,pageSize};
        return runner.query(sql,new BeanListHandler<Book>(Book.class),params);
    }

    public List<Book> queryReturnBookByPage(int currentPage,int pageSize,String userId) throws SQLException {
        QueryRunner runner=new QueryRunner(DataSourceUtil.getDataSource());
        String sql="select * from books,records where books.bookId=records.bookId and records.userId=? and records.returnDate=? limit ?,?";
        Object[] params={userId,"null",currentPage*pageSize,pageSize};
        return runner.query(sql,new BeanListHandler<Book>(Book.class),params);
    }

    public List<Book> queryReturnBookByPageRequire(int currentPage,int pageSize,String userId,Object param) throws SQLException {
        QueryRunner runner=new QueryRunner(DataSourceUtil.getDataSource());
        String sql="select * from books,records where books.bookId=records.bookId and records.userId=? and records.returnDate=? and (books.bookId=? or books.bookName=?) limit ?,?";
        Object[] params={userId,"null",param,param,currentPage*pageSize,pageSize};
        return runner.query(sql,new BeanListHandler<Book>(Book.class),params);
    }

}
